package com.sqlite{
	import com.event.SQLiteEvent;
	
	import flash.data.SQLConnection;
	import flash.data.SQLMode;
	import flash.data.SQLResult;
	import flash.data.SQLSchemaResult;
	import flash.data.SQLStatement;
	import flash.data.SQLTableSchema;
	import flash.errors.SQLError;
	import flash.events.EventDispatcher;
	import flash.events.SQLErrorEvent;
	import flash.events.SQLEvent;
	import flash.filesystem.File;
	import flash.net.Responder;
	import flash.utils.Dictionary;
	import flash.utils.setTimeout;
	
	/**
	 * DB数据库 参考：http://www.dbmng.com/Article_1688.html
	 * @author clong 2019.6.27
	 */
	public class SQLite extends EventDispatcher{
		
		/**
		 * 数据库连接字串
		 */
		private var conn:SQLConnection = null;
		
		/**
		 * 数据库url
		 */
		private var _dbUrl:String = "";
		
		/**
		 * 数据库名字
		 */
		private var _dbName:String = "";
		
		/**
		 * 链接成功
		 */
		public var connectComplete:Function = null;
		
		/**
		 * 表字典
		 */
		private var tabelMap:Dictionary = new Dictionary();
		
		public function SQLite( dbURL:String , connectComplete:Function = null ) {
			
			this.connectComplete = connectComplete;
			
			this.setLocalSQLServer( dbURL );
		}
		
		/**
		 * 打开数据库文件
		 * @param	file db数据库文件
		 */
		public function openDBFile( file:File ):void{
			
			if ( file && file.extension == "db"){
				
				if ( this.conn != null ){
					this.conn.close();
					this.conn = null;
				}
				this.setLocalSQLServer( file.url );
			}
		}
		
		/**
		 * 连接数据库
		 * @param dbURL app目录下
		 */		
		private function setLocalSQLServer(dbURL:String):Boolean {
			
			try {				
				this._dbUrl = dbURL;
				var dbFile:File = new File( dbURL );// File.applicationDirectory.resolvePath(dbURL);
				this._dbName = dbFile.name.replace( dbFile.type , "" );
				if (this.conn == null ){
					this.conn = new SQLConnection();
					this.conn.addEventListener(SQLEvent.OPEN, this.openHandler); 
					this.conn.addEventListener(SQLErrorEvent.ERROR, this.errorHandler); 
				}
				if ( this.conn.connected) {					
					return true;
				}				
				if (dbFile.exists) {//文件是否存在
					this.conn.open(dbFile, SQLMode.UPDATE);
					this.conn.loadSchema();
					
					this.setTables();
					
				} else {
					this.conn.open(dbFile);	//如果没有文件会自动创建一个数据库文件			
				}
			} catch (error:Error) {				
				return false;				
			}			
			return true;			
		}
		
		public function get dbUrl():String{
			
			return this._dbUrl;
		}
		
		/**
		 * 数据库
		 */
		public function get dbName():String{
			
			return this._dbName;
		}
		
		/**
		 * 数据库是否已链接
		 */
		public function get connected():Boolean{
			
			return this.conn && this.conn.connected;
		}
		
		/**
		 * 链接数据库成功
		 * @param	e
		 */
		protected function openHandler( e:SQLEvent ):void{
			
			this.dispatchEvent( e );			
			
			if ( this.connectComplete != null ){
				//this.connectComplete.apply( null );
//				GTimers.inst.callDelay( 1 , this.connectComplete , this );
				setTimeout( this.connectComplete , 1000 );
			}
		}
		
		/**
		 * 链接数据库失败
		 * @param	e
		 */
		protected function errorHandler( e:SQLErrorEvent ):void{
			
			this.dispatchEvent( e );
		}
		
		/**
		 * 设置表单数据
		 */
		private function setTables():void{
			
			var result:SQLSchemaResult = null;			
			if ( this.conn.connected ){
				this.conn.loadSchema();
				result = this.conn.getSchemaResult();
				if ( result && result.tables ){
					var tab:SQLTable = null;
					for ( var i:int = 0; i < result.tables.length; i++ ){
						var stc:SQLTableSchema =  result.tables[i];
						tab = this.tabelMap[ stc.name ];
						if (tab){
							continue;
						}						
						tab = new SQLTable( stc );
						this.tabelMap[ tab.tableName ] = tab;
					}
				}
			}
		}
		
		/**
		 * 表列表
		 * @return Array
		 */
		public function getTableList():Array{
			
			var tabs:Array = [];
			var tab:SQLTable = null;
			for ( var key:String in this.tabelMap ){
				tab = this.tabelMap[ key ];
				if ( tab ){
					tabs.push( tab );
				}
			}
			return tabs;
		}
		
		/**
		 * 获取数据库的所有表
		 * @return Vector.<SQLTable>
		 */
		public function getTables():Vector.<SQLTable>{
			//var result:SQLSchemaResult = null;
			//try{
				//TLog.addLog( "------------加载数据库地址" + this._dbUrl );
				////var dbFile:File = new File( this._dbUrl );
				//if ( this.conn.connected ){
					//this.conn.loadSchema();
					//result = this.conn.getSchemaResult();
				//}				
			//}catch(e:Error){
				//trace(e.getStackTrace());
			//}
			//return result ? result.tables : null;
			
			var tabs:Vector.<SQLTable> = new Vector.<SQLTable>();
			var tab:SQLTable = null;
			for ( var key:String in this.tabelMap ){
				tab = this.tabelMap[ key ];
				if ( tab ){
					tabs.push( tab );
				}
			}
			return tabs;
		}
		
		/**
		 * 获取对应名字的表
		 * @param	name
		 * @return
		 */
		public function getTable( name:String ):SQLTable{
			
			//var tables:Array = this.getTables();
			//var table:SQLTableSchema = null;
			//for each( table in tables ){
				//if ( table && table.name == name ){
					//return table;
				//}
			//}
			//return null;
			return this.tabelMap[ name ];
		}
		
		/**
		 * 是否有某张表
		 * @param	name 表名
		 * @return
		 */
		public function hasTable( name:String ):Boolean{
			
			return this.getTable( name ) != null;
		}
		
		/**
		 * 添加表
		 * @param	tab
		 */
		public function addTable( tab:SQLTable , complete:Function = null ):void{
			
			if ( tab ){
				if ( this.tabelMap[ tab.tableName ] == null ){
					this.tabelMap[ tab.tableName ] = tab;
					var sql:String = tab.toCreateSQLString();
					this.executeSQL( sql , null , complete );
				}else{
					trace( "数据库中已有该表！" );
				}
			}			
		}
		
		/**
		 * 移除表
		 * @param	name
		 */
		public function removeTable( name:String ):void{			
			
			if ( this.hasTable(name) ){
				var _self:SQLite = this;
				var sql:String = SQLiteUtils.deleteTable( name );//"drop table " + name
				this.executeSQL( sql , null , function complete():void{
					delete _self.tabelMap[ name ];
					_self.dispatchEvent( new SQLiteEvent(SQLiteEvent.REMOVE_TABLE,name) );
				});
			}			
		}
		
		/**
		 * 移除所有表
		 */
		public function removeAllTable():void{
			
			while ( this.tableNum > 0 ){
				for ( var table:String in this.tabelMap ){
					this.removeTable( table );
				}
			}
		}
		
		/**
		 * 表数量
		 */
		public function get tableNum():int{
			
			var num:uint = 0;
			for ( var key:String in this.tabelMap ){
				num++;
			}
			return num;
		}
		
		/**
		 * 查询表
		 * @param	name 		表名
		 * @param 	complete 	查询完成
		 */
		public function queryTable( name:String , complete:Function = null ):void{			
			
			var sql:String = SQLiteUtils.queryTable( name );// "SELECT * FROM " + name			
			this.executeSQL( sql , null , complete );
		}
		
		/**
		 * 执行SQL语句，返回影响的记录数	
		 * @param sql 		ql执行语句
		 * @param cmdParams 执行参数
		 * @param complete 	完成事件
		 * @param errorFunc	错误回调
		 */	
		public function executeSQL( sql:String, cmdParams:SqlParameter=null , complete:Function = null , errorFunc:Function = null):void {
			
			if ( !sql ){
				this.dispatchEvent( new SQLiteEvent(SQLiteEvent.ERROR, "执行SQL语句错误！") );
				return;
			}
			var _this:SQLite = this;
			var stmt:SQLStatement = new SQLStatement();
			stmt.sqlConnection = this.conn;			
			stmt.text = sql;
			try {				
				if (cmdParams && cmdParams.length > 0) {					
					cmdParams.transParameters(stmt);					
				}		
				//stmt.addEventListener(SQLEvent.RESULT, function onResult(e:SQLEvent):void{
					//
					//stmt.removeEventListener( SQLEvent.RESULT, onResult );
					//sr = stmt.getResult();	
					//_this.dispatchEvent( new SQLiteEvent(SQLiteEvent.COMPLETE,sr.data) );
				//});
				stmt.execute( -1 , new Responder(resultHandler ) );
			} catch (error:SQLError) {
				
				if ( errorFunc != null ){
					errorFunc( null );
				}
				_this.dispatchEvent( new SQLiteEvent(SQLiteEvent.ERROR, error.details) );
			}
			//执行结果
			function resultHandler( result:SQLResult ):void{
				
				var r:SQLSchemaResult = _this.conn.getSchemaResult();
				if (complete != null){
					complete.apply( null , [ result ] );
				}				
				_this.dispatchEvent( new SQLiteEvent(SQLiteEvent.COMPLETE, result ) );
			}
		}
		
		/**
		 * 检测一个记录是否存在
		 */		
		//public function exists( sql:String, cmdParams:SqlParameter , complete:Function = null ):void {			
			//
			//this.query( sql , cmdParams , complete );
			////var result:Array = this.query( sql , cmdParams);
			////return result.length > 0;
		//}
		
		/**
		 * 获取表某个字段的最大值
		 */
		//public function getMaxID( FieldName:String, TableName:String):uint {
			//var sql:String = "SELECT MAX(" + FieldName + ") FROM " + TableName;
			//var result:Array = this.query( sql, new SqlParameter());
			//if (result[0]["MAX(" + FieldName + ")"] != null)
				//return result[0]["MAX(" + FieldName + ")"];
			//else
				//return 0;			
		//}
		
		/**
		 * 总记录数
		 * @param tableName 表名
		 * @param fieldName 字段名
		 * @param cmdParams 执行参数
		 * @param wheresql 条件语句
		 */
		//public function getRecordNum( tableName:String, fieldName:String, cmdParams:SqlParameter, wheresql:String = ""):uint {
			//var sql:String = "SELECT COUNT(" + tableName + ") FROM " + fieldName;
			//sql += " " + wheresql;
			//var result:Array = this.query( sql , cmdParams );
			//
			//return uint(result[0]["COUNT(" + tableName + ")"].toString());
		//}
		
		/**
		 * 查找数据库每页数量
		 * @param sql 数据库语句
		 * @param cmdParams 执行参数
		 * @param pageSize 每页数据量
		 * @param pageIndex 页数
		 */		
		//public function pageList( sql:String, cmdParams:SqlParameter, pageSize:uint, pageIndex:uint):Array {
			//
			//sql += " LIMIT " + (pageSize * (pageIndex - 1)).toString() + ", " + pageSize.toString();
			//
			//return this.query( sql, cmdParams);
		//}
		
		/**
		 * 清除缓存
		 */
		public function clearCache():void {
			var stmt:SQLStatement = new SQLStatement();
			stmt.sqlConnection = this.conn;
			stmt.text = "DELETE FROM statements";
			stmt.execute();
		}
		
		/**
		 * 清理数据
		 */
		public function clear():void{
			
			if ( this.conn != null ){
				this.conn.close();
				this.conn = null;
			}
			this._dbUrl = "";
			this._dbName = "";
			while ( this.tableNum > 0 ){
				for ( var table:String in this.tabelMap ){
					delete this.tabelMap[ table ];
				}
			}
		}
	}
}
